from fastapi import APIRouter, HTTPException

# 在 user_api.py 文件中
from fastApi.database.base import get_db


router = APIRouter()

# 依赖项已经定义在base.py中，这里直接使用get_db()函数

@router.get("/products/")
def read_products(skip: int = 0, limit: int = 100):
    # skip ： 跳过指定数量的记录
    # limit ： 指定返回的记录数量

    # 查询产品
    conn = get_db()
    rows = []
    conn.operation_sql(f"SELECT * FROM products LIMIT {limit} OFFSET {skip}")
    # 获取查询到的数据   record()返回一个QSqlRecord对象 包含查询结果的字段数量 字段名信息
    result = conn.query.record()
    while conn.query.next():  # 遍历每一行的数据
        col = []
        for i in range(result.count()):
            col.append(conn.query.value(i))
        rows.append(col)
    print( rows)

    products = []
    for row in rows:
        products.append({
            "id": row[0],
            "name": row[1],
            "description": row[2],
            "price": row[3],
            "quantity": row[4],
            "category_id": row[5]
        })
    #
    return products


@router.get("/products/{product_id}")
def read_product(product_id: int):
    pass
    # conn = get_db()
    # cursor = conn.cursor()
    # cursor.execute("SELECT id, name, description, price, quantity, category_id FROM products WHERE id = ?",
    #                (product_id,))
    # row = cursor.fetchone()
    # conn.close()
    #
    # if row is None:
    #     raise HTTPException(status_code=404, detail="Product not found")
    #
    # return {
    #     "id": row[0],
    #     "name": row[1],
    #     "description": row[2],
    #     "price": row[3],
    #     "quantity": row[4],
    #     "category_id": row[5]
    # }


@router.post("/products/")
def create_product(product: dict):
    pass
    # conn = get_db()
    # cursor = conn.cursor()
    # cursor.execute("""
    #     INSERT INTO products (name, description, price, quantity, category_id)
    #     VALUES (?, ?, ?, ?, ?)
    # """, (
    #     product.get("name"),
    #     product.get("description"),
    #     product.get("price"),
    #     product.get("quantity"),
    #     product.get("category_id")
    # ))
    # conn.commit()
    # product_id = cursor.lastrowid
    # conn.close()
    #
    # return {**product, "id": product_id}


@router.put("/products/{product_id}")
def update_product(product_id: int, product: dict):
    pass
    # conn = get_db()
    # cursor = conn.cursor()
    #
    # # 检查商品是否存在
    # cursor.execute("SELECT id FROM products WHERE id = ?", (product_id,))
    # if cursor.fetchone() is None:
    #     conn.close()
    #     raise HTTPException(status_code=404, detail="Product not found")
    #
    # # 构建更新语句
    # update_fields = []
    # update_values = []
    #
    # if "name" in product:
    #     update_fields.append("name = ?")
    #     update_values.append(product["name"])
    #
    # if "description" in product:
    #     update_fields.append("description = ?")
    #     update_values.append(product["description"])
    #
    # if "price" in product:
    #     update_fields.append("price = ?")
    #     update_values.append(product["price"])
    #
    # if "quantity" in product:
    #     update_fields.append("quantity = ?")
    #     update_values.append(product["quantity"])
    #
    # if "category_id" in product:
    #     update_fields.append("category_id = ?")
    #     update_values.append(product["category_id"])
    #
    # if update_fields:
    #     update_query = f"UPDATE products SET {', '.join(update_fields)} WHERE id = ?"
    #     update_values.append(product_id)
    #     cursor.execute(update_query, update_values)
    #     conn.commit()
    #
    # conn.close()
    # return {"message": "Product updated successfully"}


@router.delete("/products/{product_id}")
def delete_product(product_id: int):
    pass
    # conn = get_db()
    # cursor = conn.cursor()
    #
    # # 检查商品是否存在
    # cursor.execute("SELECT id FROM products WHERE id = ?", (product_id,))
    # if cursor.fetchone() is None:
    #     conn.close()
    #     raise HTTPException(status_code=404, detail="Product not found")
    #
    # # 删除商品
    # cursor.execute("DELETE FROM products WHERE id = ?", (product_id,))
    # conn.commit()
    # conn.close()
    #
    # return {"message": "Product deleted successfully"}